//
//  ChartDetailDao.swift
//  KoiAccounting
//
//  Created by 纪洪文 on 05.04.25.
//

import Foundation
import GRDB

extension DetailDao {
    
    // 周总金额，找到一级和二级类别全部数据
    func getWeekTotalAmount(weekDays: Array<String>, categoryType: String, uid: String, bookId: String, cid: String? = nil) -> Double {
        let weekDaysArgs = weekDays.map{ _ in "?"}.joined(separator: ",")
        var sql = "select sum(amount) as total_amount from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and strftime('%Y-%m-%d', datetime(date, 'localtime')) in (\(weekDaysArgs))"
        var arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType] + weekDays
        // 如果有 parentId ，sql 查询条件加上 parentId
        if let cid = cid {
            // (category_parent_id = ? or cid = ?) 统计所有的父类别总和，也包含父类别本身
            sql = "select sum(amount) as total_amount from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and (category_parent_id = ? or cid = ?) and strftime('%Y-%m-%d', datetime(date, 'localtime')) in (\(weekDaysArgs))"
            arguments = [uid, bookId, categoryType, cid, cid] + weekDays
        }
        do {
            return try dbQueue.read { db in
                try Double.fetchOne(db, sql: sql, arguments: StatementArguments(arguments)) ?? 0
            }
        } catch {
            logger.error("getWeekTotalAmount fail \(error)")
        }
        return 0
    }
    
    // 周总金额，根据具体类别id查找
    func getWeekTotalAmountByCid(weekDays: Array<String>, categoryType: String, uid: String, bookId: String, cid: String) -> Double {
        let weekDaysArgs = weekDays.map{ _ in "?"}.joined(separator: ",")
        let sql = "select sum(amount) as total_amount from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and cid = ? and strftime('%Y-%m-%d', datetime(date, 'localtime')) in (\(weekDaysArgs))"
        let arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType, cid] + weekDays
        do {
            return try dbQueue.read { db in
                try Double.fetchOne(db, sql: sql, arguments: StatementArguments(arguments)) ?? 0
            }
        } catch {
            logger.error("getWeekTotalAmount fail \(error)")
        }
        return 0
    }
    
    // 月总金额，找到一级和二级类别全部数据
    func getMonthTotalAmount(month: String, categoryType: String, uid: String, bookId: String, cid: String? = nil) -> Double {
        var sql = "select sum(amount) as total_amount from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and strftime('%Y-%m', datetime(date, 'localtime')) = ?"
        var arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType, month]
        if let cid = cid {
            // (category_parent_id = ? or cid = ?) 统计所有的父类别总和，也包含父类别本身
            sql = "select sum(amount) as total_amount from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and (category_parent_id = ? or cid = ?) and strftime('%Y-%m', datetime(date, 'localtime')) = ?"
            arguments = [uid, bookId, categoryType, cid, cid, month]
        }
        do {
            return try dbQueue.read { db in
                try Double.fetchOne(db, sql: sql, arguments: StatementArguments(arguments)) ?? 0
            }
        } catch {
            logger.error("getMonthTotalAmount fail \(error)")
        }
        return 0
    }
    
    // 月总金额，根据具体类别id查找
    func getMonthTotalAmountByCid(month: String, categoryType: String, uid: String, bookId: String, cid: String) -> Double {
        let sql = "select sum(amount) as total_amount from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and cid = ? and strftime('%Y-%m', datetime(date, 'localtime')) = ?"
        let arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType, cid, month]
        do {
            return try dbQueue.read { db in
                try Double.fetchOne(db, sql: sql, arguments: StatementArguments(arguments)) ?? 0
            }
        } catch {
            logger.error("getMonthTotalAmount fail \(error)")
        }
        return 0
    }
    
    // 年总金额，找到一级和二级类别全部数据
    func getYearTotalAmount(year: String, categoryType: String, uid: String, bookId: String, cid: String? = nil) -> Double {
        var sql = "select sum(amount) as total_amount from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and strftime('%Y', datetime(date, 'localtime')) = ?"
        var arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType, year]
        if let cid = cid {
            // (category_parent_id = ? or cid = ?) 统计所有的父类别总和，也包含父类别本身
            sql = "select sum(amount) as total_amount from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and (category_parent_id = ? or cid = ?) and strftime('%Y', datetime(date, 'localtime')) = ?"
            arguments = [uid, bookId, categoryType, cid, cid, year]
        }
        do {
            return try dbQueue.read { db in
                try Double.fetchOne(db, sql: sql, arguments: StatementArguments(arguments)) ?? 0
            }
        } catch {
            logger.error("getMonthTotalAmount fail \(error)")
        }
        return 0
    }
    
    // 年总金额，根据具体类别id查找
    func getYearTotalAmountByCid(year: String, categoryType: String, uid: String, bookId: String, cid: String) -> Double {
        let sql = "select sum(amount) as total_amount from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and cid = ? and strftime('%Y', datetime(date, 'localtime')) = ?"
        let arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType, cid, year]
        do {
            return try dbQueue.read { db in
                try Double.fetchOne(db, sql: sql, arguments: StatementArguments(arguments)) ?? 0
            }
        } catch {
            logger.error("getMonthTotalAmount fail \(error)")
        }
        return 0
    }
    
    // 周每日金额汇总 ，找到一级和二级类别全部数据
    func getDayAmountByWeekDays(weekDays: Array<String>, categoryType: String, uid: String, bookId: String, cid: String? = nil) -> [String : Double] {
        let weekDaysArgs = weekDays.map{ _ in "?"}.joined(separator: ",")
        var sql = "select strftime('%Y-%m-%d', datetime(date, 'localtime')) as day, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and strftime('%Y-%m-%d', datetime(date, 'localtime')) in (\(weekDaysArgs)) group by day"
        var arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType] + weekDays
        if let cid = cid {
            sql = "select strftime('%Y-%m-%d', datetime(date, 'localtime')) as day, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and (category_parent_id = ? or cid = ?) and strftime('%Y-%m-%d', datetime(date, 'localtime')) in (\(weekDaysArgs)) group by day"
            arguments = [uid, bookId, categoryType, cid, cid] + weekDays
        }
        do {
            return try dbQueue.read { db in
                let rows = try Row.fetchCursor(db, sql: sql, arguments: StatementArguments(arguments))
                
                var result = [String : Double]()
                while let row = try rows.next() {
                    let day: String = row["day"]
                    let amountTotal: Double = row["amount_total"]
                    result[day] = amountTotal
                }
                return result
            }
        } catch {
            logger.error("getDayAmountByWeekDays fail \(error)")
        }
        return [String : Double]()
    }
    
    // 周每日金额汇总 根据具体类别id查找
    func getDayAmountByWeekDaysByCid(weekDays: Array<String>, categoryType: String, uid: String, bookId: String, cid: String? = nil) -> [String : Double] {
        let weekDaysArgs = weekDays.map{ _ in "?"}.joined(separator: ",")
        let sql = "select strftime('%Y-%m-%d', datetime(date, 'localtime')) as day, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and cid = ? and strftime('%Y-%m-%d', datetime(date, 'localtime')) in (\(weekDaysArgs)) group by day"
        let arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType, cid] + weekDays
        do {
            return try dbQueue.read { db in
                let rows = try Row.fetchCursor(db, sql: sql, arguments: StatementArguments(arguments))
                
                var result = [String : Double]()
                while let row = try rows.next() {
                    let day: String = row["day"]
                    let amountTotal: Double = row["amount_total"]
                    result[day] = amountTotal
                }
                return result
            }
        } catch {
            logger.error("getDayAmountByWeekDays fail \(error)")
        }
        return [String : Double]()
    }
    
    // 月每日金额汇总
    func getDayAmountByMonth(month: String, categoryType: String, uid: String, bookId: String, cid: String? = nil) -> [String : Double] {
        var sql = "select strftime('%Y-%m-%d', datetime(date, 'localtime')) as day, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and strftime('%Y-%m', datetime(date, 'localtime')) = ? group by day"
        var arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType, month]
        if let cid = cid {
            sql = "select strftime('%Y-%m-%d', datetime(date, 'localtime')) as day, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and (category_parent_id = ? or cid = ?) and strftime('%Y-%m', datetime(date, 'localtime')) = ? group by day"
            arguments = [uid, bookId, categoryType, cid, cid, month]
        }
        do {
            return try dbQueue.read { db in
                let rows = try Row.fetchCursor(db, sql: sql, arguments: StatementArguments(arguments))
                var result = [String : Double]()
                while let row = try rows.next() {
                    let day: String = row["day"]
                    let amountTotal: Double = row["amount_total"]
                    result[day] = amountTotal
                }
                return result
            }
        } catch {
            logger.error("getDayAmountByMonth fail \(error)")
        }
        return [String : Double]()
    }
    
    // 月每日金额汇总
    func getDayAmountByMonthByCid(month: String, categoryType: String, uid: String, bookId: String, cid: String) -> [String : Double] {
        let sql = "select strftime('%Y-%m-%d', datetime(date, 'localtime')) as day, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and cid = ? and strftime('%Y-%m', datetime(date, 'localtime')) = ? group by day"
        let arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType, cid, month]
        do {
            return try dbQueue.read { db in
                let rows = try Row.fetchCursor(db, sql: sql, arguments: StatementArguments(arguments))
                var result = [String : Double]()
                while let row = try rows.next() {
                    let day: String = row["day"]
                    let amountTotal: Double = row["amount_total"]
                    result[day] = amountTotal
                }
                return result
            }
        } catch {
            logger.error("getDayAmountByMonth fail \(error)")
        }
        return [String : Double]()
    }
    
    // 年每月金额汇总
    func getDayAmountByYear(year: String, categoryType: String, uid: String, bookId: String, cid: String? = nil) -> [String : Double] {
        var sql = "select strftime('%Y-%m', datetime(date, 'localtime')) as day, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and strftime('%Y', datetime(date, 'localtime')) = ? group by day"
        var arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType, year]
        if let cid = cid {
            sql = "select strftime('%Y-%m', datetime(date, 'localtime')) as day, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and (category_parent_id = ? or cid = ?) and strftime('%Y', datetime(date, 'localtime')) = ? group by day"
            arguments = [uid, bookId, categoryType, cid, cid, year]
        }
        do {
            return try dbQueue.read { db in
                let rows = try Row.fetchCursor(db, sql: sql, arguments: StatementArguments(arguments))
                var result = [String : Double]()
                while let row = try rows.next() {
                    let day: String = row["day"]
                    let amountTotal: Double = row["amount_total"]
                    result[day] = amountTotal
                }
                return result
            }
        } catch {
            logger.error("getDayAmountByYear fail \(error)")
        }
        return [String : Double]()
    }
    
    // 年每月金额汇总
    func getDayAmountByYearByCid(year: String, categoryType: String, uid: String, bookId: String, cid: String) -> [String : Double] {
        let sql = "select strftime('%Y-%m', datetime(date, 'localtime')) as day, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and cid = ? and strftime('%Y', datetime(date, 'localtime')) = ? group by day"
        let arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType, cid, year]
        do {
            return try dbQueue.read { db in
                let rows = try Row.fetchCursor(db, sql: sql, arguments: StatementArguments(arguments))
                var result = [String : Double]()
                while let row = try rows.next() {
                    let day: String = row["day"]
                    let amountTotal: Double = row["amount_total"]
                    result[day] = amountTotal
                }
                return result
            }
        } catch {
            logger.error("getDayAmountByYear fail \(error)")
        }
        return [String : Double]()
    }
    
    // 周类别金额汇总
    func getPieDataByWeekDays(weekDays: Array<String>, categoryType: String, uid: String, bookId: String, cids: Array<String>? = nil) -> [CategoryAmount] {
        let weekDaysArgs = weekDays.map{ _ in "?"}.joined(separator: ",")
        var sql = "select category_db_id, cid, category_name, category_icon, category_type, category_parent_id as parent_id, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and strftime('%Y-%m-%d', datetime(date, 'localtime')) in (\(weekDaysArgs)) group by cid order by amount_total desc"
        var arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType] + weekDays
        if let cids = cids {
            let cidsArgs = cids.map{ _ in "?" }.joined(separator: ",")
            sql = "select category_db_id, cid, category_name, category_icon, category_type, category_parent_id as parent_id, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and cid in(\(cidsArgs)) and strftime('%Y-%m-%d', datetime(date, 'localtime')) in (\(weekDaysArgs)) group by cid order by amount_total desc"
            arguments = [uid, bookId, categoryType] + cids + weekDays
        }
        do {
            return try dbQueue.read { db in
                var dataList = [CategoryAmount]()
                let rows = try Row.fetchCursor(db, sql: sql, arguments: StatementArguments(arguments))
                while let row = try rows.next() {
                    let itemData = CategoryAmount(category: AccountingCategory(
                        id: row["category_db_id"] as Int64,
                        cid:row["cid"] as String,
                        name: row["category_name"] as String?,
                        type: row["category_type"] as String?,
                        icon: row["category_icon"] as String?,
                        parentId: row["parent_id"] as String?
                    ), amount: row["amount_total"] as Double)
                    dataList.append(itemData)
                }
                return dataList
            }
        } catch {
            logger.error("getPieDataByWeekDays fail \(error)")
        }
        return Array()
    }
    
    // 月类别金额汇总
    func getPieDataByMonth(month: String, categoryType: String, uid: String, bookId: String, cids: Array<String>? = nil) -> [CategoryAmount] {
        var sql = "select category_db_id, cid, category_name, category_icon, category_type, category_parent_id as parent_id, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and strftime('%Y-%m', datetime(date, 'localtime')) = ? group by cid order by amount_total desc"
        var arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType, month]
        if let cids = cids {
            let cidsArgs = cids.map{ _ in "?" }.joined(separator: ",")
            sql = "select category_db_id, cid, category_name, category_icon, category_type, category_parent_id as parent_id, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and strftime('%Y-%m', datetime(date, 'localtime')) = ? and cid in (\(cidsArgs)) group by cid order by amount_total desc"
            arguments = [uid, bookId, categoryType, month] + cids
        }
        do {
            return try dbQueue.read { db in
                var dataList = [CategoryAmount]()
                let rows = try Row.fetchCursor(db, sql: sql, arguments: StatementArguments(arguments))
                while let row = try rows.next() {
                    let itemData = CategoryAmount(category: AccountingCategory(
                        id: row["category_db_id"] as Int64,
                        cid:row["cid"] as String,
                        name: row["category_name"] as String?,
                        type: row["category_type"] as String?,
                        icon: row["category_icon"] as String?,
                        parentId: row["parent_id"] as String?
                    ), amount: row["amount_total"] as Double)
                    dataList.append(itemData)
                }
                return dataList
            }
        } catch {
            logger.error("getPieDataByMonth fail \(error)")
        }
        return Array()
    }
    
    // 年类别金额汇总
    func getPieDataByYear(year: String, categoryType: String, uid: String, bookId: String, cids: Array<String>? = nil) -> [CategoryAmount] {
        var sql = "select category_db_id, cid, category_name, category_icon, category_type, category_parent_id as parent_id, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and strftime('%Y', datetime(date, 'localtime')) = ? group by cid order by amount_total desc"
        var arguments: [DatabaseValueConvertible] = [uid, bookId, categoryType, year]
        if let cids = cids {
            let cidsArgs = cids.map{ _ in "?" }.joined(separator: ",")
            sql = "select category_db_id, cid, category_name, category_icon, category_type, category_parent_id as parent_id, sum(amount) as amount_total from accounting_detail_combine_view where uid = ? and book_id = ? and category_type = ? and strftime('%Y', datetime(date, 'localtime')) = ? and cid in (\(cidsArgs))  group by cid order by amount_total desc"
            arguments = [uid, bookId, categoryType, year] + cids
        }
        do {
            return try dbQueue.read { db in
                var dataList = [CategoryAmount]()
                let rows = try Row.fetchCursor(db, sql: sql, arguments: StatementArguments(arguments))
                while let row = try rows.next() {
                    let itemData = CategoryAmount(category: AccountingCategory(
                        id: row["category_db_id"] as Int64,
                        cid:row["cid"] as String,
                        name: row["category_name"] as String?,
                        type: row["category_type"] as String?,
                        icon: row["category_icon"] as String?,
                        parentId: row["parent_id"] as String?
                    ), amount: row["amount_total"] as Double)
                    dataList.append(itemData)
                }
                return dataList
            }
        } catch {
            logger.error("getPieDataByYear fail \(error)")
        }
        return Array()
    }

}
